// 财务统计路由
const express = require('express');
const router = express.Router();
const { query } = require('../config/database');
const { authenticateToken, requireAdmin } = require('../middleware/auth');
const { formatAmount } = require('../utils/helpers');
const ResponseHelper = require('../utils/response');

// 获取财务概览
router.get('/overview', authenticateToken, requireAdmin, async (req, res) => {
  try {
    // 获取总收入
    const [totalRevenue] = await query(`
      SELECT COALESCE(SUM(total_cost), 0) as total
      FROM orders 
      WHERE status = 'completed'
    `);

    // 获取今日收入
    const [todayRevenue] = await query(`
      SELECT COALESCE(SUM(total_cost), 0) as total
      FROM orders 
      WHERE status = 'completed' 
        AND DATE(created_at) = CURDATE()
    `);

    // 获取本月收入
    const [monthRevenue] = await query(`
      SELECT COALESCE(SUM(total_cost), 0) as total
      FROM orders 
      WHERE status = 'completed' 
        AND YEAR(created_at) = YEAR(CURDATE()) 
        AND MONTH(created_at) = MONTH(CURDATE())
    `);

    // 获取订单统计
    const [orderStats] = await query(`
      SELECT 
        COUNT(*) as total_orders,
        SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed_orders,
        SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending_orders,
        SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) as cancelled_orders
      FROM orders
    `);

    // 获取用户统计
    const [userStats] = await query(`
      SELECT 
        COUNT(*) as total_users,
        SUM(balance) as total_balance
      FROM users
    `);

    // 获取台球桌统计
    const [tableStats] = await query(`
      SELECT 
        COUNT(*) as total_tables,
        SUM(CASE WHEN status = 'occupied' THEN 1 ELSE 0 END) as occupied_tables
      FROM tables
    `);

    ResponseHelper.success(res, {
      revenue: {
        total: formatAmount(totalRevenue.total),
        today: formatAmount(todayRevenue.total),
        month: formatAmount(monthRevenue.total)
      },
      orders: orderStats,
      users: {
        ...userStats,
        total_balance: formatAmount(userStats.total_balance)
      },
      tables: tableStats
    }, '获取财务概览成功');

  } catch (error) {
    console.error('获取财务概览错误:', error);
    ResponseHelper.serverError(res, '获取财务概览失败');
  }
});

// 获取收入趋势
router.get('/revenue-trend', authenticateToken, requireAdmin, async (req, res) => {
  try {
    const { period = '30', type = 'daily' } = req.query;

    let dateFormat, groupBy;
    switch (type) {
      case 'hourly':
        dateFormat = '%Y-%m-%d %H:00:00';
        groupBy = 'DATE_FORMAT(created_at, "%Y-%m-%d %H:00:00")';
        break;
      case 'monthly':
        dateFormat = '%Y-%m';
        groupBy = 'DATE_FORMAT(created_at, "%Y-%m")';
        break;
      default: // daily
        dateFormat = '%Y-%m-%d';
        groupBy = 'DATE(created_at)';
    }

    const revenueTrend = await query(`
      SELECT 
        ${groupBy} as date,
        COUNT(*) as order_count,
        SUM(total_cost) as revenue,
        AVG(total_cost) as avg_order_value
      FROM orders 
      WHERE status = 'completed' 
        AND created_at >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
      GROUP BY ${groupBy}
      ORDER BY date
    `, [period]);

    ResponseHelper.success(res, {
      trend: revenueTrend,
      period: parseInt(period),
      type
    }, '获取收入趋势成功');

  } catch (error) {
    console.error('获取收入趋势错误:', error);
    ResponseHelper.serverError(res, '获取收入趋势失败');
  }
});

// 获取台球桌收入分析
router.get('/table-analysis', authenticateToken, requireAdmin, async (req, res) => {
  try {
    const { period = '30' } = req.query;

    const tableAnalysis = await query(`
      SELECT 
        t.id,
        t.name,
        t.type,
        t.hourly_rate,
        COUNT(o.id) as order_count,
        SUM(o.duration) as total_hours,
        SUM(o.total_cost) as total_revenue,
        AVG(o.total_cost) as avg_revenue_per_order,
        (SUM(o.duration) / ?) * 100 as utilization_rate
      FROM tables t
      LEFT JOIN orders o ON t.id = o.table_id 
        AND o.status = 'completed'
        AND o.created_at >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
      GROUP BY t.id, t.name, t.type, t.hourly_rate
      ORDER BY total_revenue DESC
    `, [period * 24, period]); // 假设每天24小时可用

    ResponseHelper.success(res, {
      analysis: tableAnalysis,
      period: parseInt(period)
    }, '获取台球桌收入分析成功');

  } catch (error) {
    console.error('获取台球桌收入分析错误:', error);
    ResponseHelper.serverError(res, '获取台球桌收入分析失败');
  }
});

// 获取时段收入分析
router.get('/hourly-analysis', authenticateToken, requireAdmin, async (req, res) => {
  try {
    const { period = '30' } = req.query;

    const hourlyAnalysis = await query(`
      SELECT 
        HOUR(STR_TO_DATE(start_time, '%H:%i:%s')) as hour,
        COUNT(*) as order_count,
        SUM(duration) as total_hours,
        SUM(total_cost) as total_revenue,
        AVG(total_cost) as avg_revenue_per_order
      FROM orders 
      WHERE status = 'completed'
        AND created_at >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
      GROUP BY hour
      ORDER BY hour
    `, [period]);

    ResponseHelper.success(res, {
      analysis: hourlyAnalysis,
      period: parseInt(period)
    }, '获取时段收入分析成功');

  } catch (error) {
    console.error('获取时段收入分析错误:', error);
    ResponseHelper.serverError(res, '获取时段收入分析失败');
  }
});

// 获取月度收入对比
router.get('/monthly-comparison', authenticateToken, requireAdmin, async (req, res) => {
  try {
    const { months = '12' } = req.query;

    const monthlyComparison = await query(`
      SELECT 
        DATE_FORMAT(created_at, '%Y-%m') as month,
        COUNT(*) as order_count,
        SUM(total_cost) as revenue,
        AVG(total_cost) as avg_order_value
      FROM orders 
      WHERE status = 'completed'
        AND created_at >= DATE_SUB(CURDATE(), INTERVAL ? MONTH)
      GROUP BY DATE_FORMAT(created_at, '%Y-%m')
      ORDER BY month
    `, [months]);

    ResponseHelper.success(res, {
      comparison: monthlyComparison,
      months: parseInt(months)
    }, '获取月度收入对比成功');

  } catch (error) {
    console.error('获取月度收入对比错误:', error);
    ResponseHelper.serverError(res, '获取月度收入对比失败');
  }
});

// 获取用户消费排行
router.get('/user-spending', authenticateToken, requireAdmin, async (req, res) => {
  try {
    const { period = '30', limit = '10' } = req.query;

    const userSpending = await query(`
      SELECT 
        u.id,
        u.username,
        u.real_name,
        u.phone,
        COUNT(o.id) as order_count,
        SUM(o.total_cost) as total_spending,
        AVG(o.total_cost) as avg_spending_per_order,
        SUM(o.duration) as total_play_time
      FROM users u
      INNER JOIN orders o ON u.id = o.user_id
      WHERE o.status = 'completed'
        AND o.created_at >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
      GROUP BY u.id, u.username, u.real_name, u.phone
      ORDER BY total_spending DESC
      LIMIT ?
    `, [period, parseInt(limit)]);

    ResponseHelper.success(res, {
      ranking: userSpending,
      period: parseInt(period),
      limit: parseInt(limit)
    }, '获取用户消费排行成功');

  } catch (error) {
    console.error('获取用户消费排行错误:', error);
    ResponseHelper.serverError(res, '获取用户消费排行失败');
  }
});

// 获取收入统计报告
router.get('/revenue-report', authenticateToken, requireAdmin, async (req, res) => {
  try {
    const { start_date, end_date } = req.query;

    if (!start_date || !end_date) {
      return ResponseHelper.error(res, '开始日期和结束日期不能为空', 400);
    }

    // 获取期间内的收入统计
    const [revenueStats] = await query(`
      SELECT
        COUNT(*) as total_orders,
        SUM(total_cost) as total_revenue,
        AVG(total_cost) as avg_order_value,
        SUM(duration) as total_hours
      FROM orders
      WHERE status = 'completed'
        AND DATE(created_at) BETWEEN ? AND ?
    `, [start_date, end_date]);

    // 获取台球桌类型收入分布
    const typeRevenue = await query(`
      SELECT
        table_type,
        COUNT(*) as order_count,
        SUM(total_cost) as revenue,
        AVG(total_cost) as avg_revenue
      FROM orders
      WHERE status = 'completed'
        AND DATE(created_at) BETWEEN ? AND ?
      GROUP BY table_type
      ORDER BY revenue DESC
    `, [start_date, end_date]);

    // 获取每日收入明细
    const dailyRevenue = await query(`
      SELECT
        DATE(created_at) as date,
        COUNT(*) as order_count,
        SUM(total_cost) as revenue
      FROM orders
      WHERE status = 'completed'
        AND DATE(created_at) BETWEEN ? AND ?
      GROUP BY DATE(created_at)
      ORDER BY date
    `, [start_date, end_date]);

    ResponseHelper.success(res, {
      period: { start_date, end_date },
      summary: {
        ...revenueStats,
        total_revenue: formatAmount(revenueStats.total_revenue),
        avg_order_value: formatAmount(revenueStats.avg_order_value)
      },
      type_distribution: typeRevenue,
      daily_breakdown: dailyRevenue
    }, '获取收入统计报告成功');

  } catch (error) {
    console.error('获取收入统计报告错误:', error);
    ResponseHelper.serverError(res, '获取收入统计报告失败');
  }
});

// 获取台球桌类型收入分布
router.get('/table-type-revenue', authenticateToken, requireAdmin, async (req, res) => {
  try {
    const { period = '30' } = req.query;

    const typeRevenue = await query(`
      SELECT
        table_type,
        COUNT(*) as order_count,
        SUM(total_cost) as total_revenue,
        AVG(total_cost) as avg_revenue_per_order,
        SUM(duration) as total_hours,
        AVG(duration) as avg_duration_per_order
      FROM orders
      WHERE status = 'completed'
        AND created_at >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
      GROUP BY table_type
      ORDER BY total_revenue DESC
    `, [period]);

    // 计算百分比
    const totalRevenue = typeRevenue.reduce((sum, item) => sum + parseFloat(item.total_revenue), 0);
    const distributionWithPercentage = typeRevenue.map(item => ({
      ...item,
      revenue_percentage: totalRevenue > 0 ? ((parseFloat(item.total_revenue) / totalRevenue) * 100).toFixed(2) : '0.00'
    }));

    ResponseHelper.success(res, {
      distribution: distributionWithPercentage,
      total_revenue: formatAmount(totalRevenue),
      period: parseInt(period)
    }, '获取台球桌类型收入分布成功');

  } catch (error) {
    console.error('获取台球桌类型收入分布错误:', error);
    ResponseHelper.serverError(res, '获取台球桌类型收入分布失败');
  }
});

// 获取支付状态统计
router.get('/payment-status', authenticateToken, requireAdmin, async (req, res) => {
  try {
    const { period = '30' } = req.query;

    // 获取订单状态统计
    const orderStatusStats = await query(`
      SELECT
        status,
        COUNT(*) as count,
        SUM(total_cost) as total_amount
      FROM orders
      WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
      GROUP BY status
    `, [period]);

    // 获取充值统计
    const rechargeStats = await query(`
      SELECT
        COUNT(*) as total_recharges,
        SUM(amount) as total_amount,
        AVG(amount) as avg_amount
      FROM recharge_records
      WHERE status = 'completed'
        AND created_at >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
    `, [period]);

    // 获取充值方式分布
    const rechargeMethodStats = await query(`
      SELECT
        payment_method,
        COUNT(*) as count,
        SUM(amount) as total_amount
      FROM recharge_records
      WHERE status = 'completed'
        AND created_at >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
      GROUP BY payment_method
    `, [period]);

    ResponseHelper.success(res, {
      order_status: orderStatusStats,
      recharge_summary: rechargeStats[0] || { total_recharges: 0, total_amount: 0, avg_amount: 0 },
      recharge_methods: rechargeMethodStats,
      period: parseInt(period)
    }, '获取支付状态统计成功');

  } catch (error) {
    console.error('获取支付状态统计错误:', error);
    ResponseHelper.serverError(res, '获取支付状态统计失败');
  }
});

module.exports = router;
